﻿import postgresql
import os
import sys

__dir__ = os.path.dirname(os.path.abspath(__file__))

path_error = os.path.join(__dir__, 'cleaning.err')
sys.stderr = open(path_error, 'a')

path_out = os.path.join(__dir__, 'cleaning.out')
sys.stdout = open(path_out, 'a')


db_new_test = postgresql.open("pq://palo:palo@sa-serv8/test")


# удаляем сотрудников "без договоров"

hr_employee = db_new_test.prepare("DELETE FROM hr_employee WHERE x_1s_sc11305_id IS NOT NULL AND resource_id IN (SELECT DISTINCT id FROM resource_resource WHERE x_1s_sc11305_id IS NOT NULL AND user_id NOT IN (SELECT DISTINCT manager_id FROM account_analytic_account))")
hr_employee()


# удаляем ресурсы "без договоров"

resource_resource=db_new_test.prepare("DELETE FROM resource_resource WHERE x_1s_sc11305_id IS NOT NULL AND user_id NOT IN (SELECT DISTINCT manager_id FROM account_analytic_account)")
resource_resource()


# удаляем подразделения "без договоров"
		
hr_department=db_new_test.prepare("DELETE FROM hr_department WHERE x_1s_id IS NOT NULL AND id NOT IN (SELECT DISTINCT department_id FROM hr_employee WHERE x_1s_sc11305_id IS NOT NULL AND resource_id IN (SELECT DISTINCT id FROM resource_resource WHERE x_1s_sc11305_id IS NOT NULL AND user_id NOT IN (SELECT DISTINCT manager_id FROM account_analytic_account)))")
hr_department()


# удаляем пользователей "без договоров"

res_users=db_new_test.prepare("DELETE FROM res_users WHERE x_1s_sc11305_id IS NOT NULL AND id NOT IN (SELECT DISTINCT manager_id FROM account_analytic_account)")
res_users()
	

# удаляем контрагентов-пользователей "без договоров"

res_partner_user=db_new_test.prepare("DELETE FROM res_partner WHERE x_1s_sc11305_id IS NOT NULL AND id IN (SELECT DISTINCT partner_id FROM res_users WHERE x_1s_sc11305_id IS NOT NULL AND id NOT IN (SELECT DISTINCT manager_id FROM account_analytic_account))")
res_partner_user()

# удаляем контрагентов без контактов

res_partner_partner=db_new_test.prepare("DELETE FROM res_partner WHERE x_1s_name is not null AND id NOT IN (SELECT DISTINCT parent_id FROM res_partner WHERE x_1s_id IS NOT NULL)")
res_partner_partner()

sys.stderr.close()
sys.stdout.close()

sys.stderr = sys.__stderr__
sys.stdout = sys.__stdout__